package codegen.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import codegen.CommonPara;
import codegen.util.database.DataType;
import codegen.util.database.Field;
import codegen.util.database.JdbcType;
import codegen.util.database.PrimaryKey;
import codegen.util.database.Table;

public class DbUtil {
	public static Connection getConnection() throws Exception {
		Class.forName(CommonPara.dbDriver);
		return DriverManager.getConnection(CommonPara.dbUrl, CommonPara.dbUser,
				CommonPara.dbPassword);
	}

	public static void readFieldListFromDB(Connection conn, String schema,
			String driver, Table table, int tableSeq) throws SQLException {
		List<Field> fieldList = new ArrayList<Field>();
		List<Field> primaryKeyList = new ArrayList<Field>();

		table.setFieldList(fieldList);
		table.setPrimaryKeyList(primaryKeyList);

		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		Map<String, PrimaryKey> primaryKeyMap = new java.util.HashMap<String, PrimaryKey>();
		try {
			DatabaseMetaData md = conn.getMetaData();

			ResultSet rs = md
					.getPrimaryKeys(null, schema, table.getTableName());
			while (rs.next()) {
				primaryKeyMap.put(rs.getString("COLUMN_NAME"), new PrimaryKey(
						rs.getInt("KEY_SEQ")));
			}
			rs.close();
			String sql = "select * from " /*+ schema + "."*/ + table.getTableName();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			ResultSetMetaData resultMetaData = rs.getMetaData();
			int iFieldNum = resultMetaData.getColumnCount();
			for (int i = 1; i <= iFieldNum; ++i) {
				DataType dataType = null;
				// TODO 参考http://blog.csdn.net/sun2445067/article/details/6492166
				dataType = DataType.transfer(resultMetaData.getColumnType(i),
						resultMetaData.getPrecision(i),
						resultMetaData.getScale(i),resultMetaData.getColumnName(i));
				JdbcType jdbcType = JdbcType.transfer(resultMetaData.getColumnType(i));
				Field field = new Field(table.getTableName(),
						table.getTableDesc(), resultMetaData.getColumnName(i),
						"", (PrimaryKey) primaryKeyMap.get(resultMetaData
								.getColumnName(i)), false, true,
								jdbcType.getName(), dataType,
						resultMetaData.getPrecision(i),
						resultMetaData.getScale(i), (tableSeq * 100 + (i - 1)),
						(i - 1), resultMetaData.isNullable(i) != 0,
						isPk(resultMetaData.getColumnName(i), primaryKeyMap));

				field.setFieldDesc(getFieldComment(conn, table.getTableName(),
						field.getFieldName()));
				fieldList.add(field);
				if (field.getPrimaryKey() != null) {
					primaryKeyList.add(field);
				}
			}
			rs.close();

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			if (pstmt1 != null) {
				pstmt1.close();
			}
		}
		initString(table);
	}

	private static boolean isPk(String fieldName,
			Map<String, PrimaryKey> primaryKeyMap) {
		Object obj = primaryKeyMap.get(fieldName);
		if (obj == null) {
			return false;
		}
		return true;
	}

	private static String getFieldComment(Connection conn, String tableName,
			String columnName) {
		//String sql = "select column_comment from information_schema.columns where table_name = ? and column_name = ?"; MYSQL
		String sql="select comments from user_col_comments where table_name = ? and column_name = ?";//ORACLE
		String s = columnName;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tableName);
			pstmt.setString(2, columnName);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				//s = rs.getString("column_comment");//MYSQL
				s = rs.getString("comments");//ORACLE
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return s;

	}
	
	private static void initString(Table table) {
		int size = table.getFieldList().size();
		for (int i = 0; i < size; i++) {
			Field field = (Field) table.getFieldList().get(i);
			table.fieldListString += field.getFieldName() + " AS "
					+ field.getObjectName();

			table.table_fieldListString += table.getTableName() + "."
					+ field.getFieldName();
			table.table_fieldAliasString += table.getTableName() + "."
					+ field.getFieldName() + " AS " + field.getObjectName();

			if(i > 0 && i%8 == 0){
				table.singlefieldListString += field.getFieldName()+"\n\t\t";
			}else{
				table.singlefieldListString += field.getFieldName();
			}
			//要做<if test=""/> 判断判断
			if(!field.getFieldName().toLowerCase().equals("id")) {
				if(field.getColumnDataType().equals("DATE") || field.getColumnDataType().equals("TIMESTAMP") || field.getColumnDataType().equals("Long") || field.getColumnDataType().equals("DOUBLE") || field.getColumnDataType().equals("Integer")) {//ORACLE
					table.inserfieldListString += "<if test=\"" + field.getObjectName() + " != null\">" + field.getFieldName();
					table.insertvaluesString += "<if test=\"" + field.getObjectName() + " != null\">" + "#{" + field.getObjectName() + "}";
				} else {
					table.inserfieldListString += "<if test=\"" + field.getObjectName() + " != null and " + field.getObjectName() + " != ''\">" + field.getFieldName();
					table.insertvaluesString += "<if test=\"" + field.getObjectName() + " != null and " + field.getObjectName() + " != ''\">" + "#{" + field.getObjectName() + "}";
				}
				if (i != table.getFieldList().size() - 1) {
					table.inserfieldListString += ",";//去掉最后的逗号
					table.inserfieldListString += "</if>\n\t\t\t\t";
					
					table.insertvaluesString += ",";//去掉最后的逗号
					table.insertvaluesString += "</if>\n\t\t\t\t";
				}else{
					table.inserfieldListString += "</if>";
					
					table.insertvaluesString += "</if>";
				}
			}
			
			if(!field.getFieldName().toLowerCase().equals("id")) {
				table.updatefieldListString += "<if test=\"" + field.getObjectName() + " != null\">" + field.getFieldName() + " = #{" + field.getObjectName() + "}";
				if (i != table.getFieldList().size() - 1) {
					table.updatefieldListString += ",";//去掉最后的逗号
					table.updatefieldListString += "</if>\n\t\t\t";
				}else{
					table.updatefieldListString += "</if>";
				}
			}
			
			//System.out.println("生成SQL条件获取的字段类型："+field.getColumnDataType() + " width:" +field.getWidth() + " DecimalWidth:" + field.getDecimalWidth());
			
			//查询条件
			if(field.getColumnDataType().equals("DATE") || field.getColumnDataType().equals("TIMESTAMP") || field.getColumnDataType().equals("Long") || field.getColumnDataType().equals("DOUBLE") || field.getColumnDataType().equals("Integer")) {//ORACLE
				table.conditionfieldliststring += "<if test=\"condition." + field.getObjectName() + "!=null\"> and " + field.getFieldName() + "=#{condition." + field.getObjectName() + "}";
			} else {
				table.conditionfieldliststring += "<if test=\"condition." + field.getObjectName() + "!=null and condition." + field.getObjectName() + "!=''\"> and " + field.getFieldName() + "=#{condition." + field.getObjectName() + "}";
			}
			
			if (i != table.getFieldList().size() - 1) {
				table.conditionfieldliststring += "</if>\n\t\t\t\t";
			}else{
				table.conditionfieldliststring += "</if>";
			}
			
			
			table.typeFieldListString += field.getDataType().getTypeString()
					+ " " + field.getObjectName();
			if (i != table.getFieldList().size() - 1) {
				table.fieldListString += ",";
				table.table_fieldListString += ",";
				
				table.singlefieldListString += ",";
				
				table.typeFieldListString += ",";
				table.table_fieldAliasString += ",";
			}else{
				
			}
			table.questionFieldListString += "?";
			if (i != table.getFieldList().size() - 1) {
				table.questionFieldListString += ",";
			}
			if (!field.isPrimaryKey()) {
				table.setAssignListString += field.getFieldName() + "=?";
				if (i != table.getFieldList().size() - 1) {
					table.setAssignListString += ",";
				}
			}
			if (field.isPrimaryKey()) {

				table.keyFieldListString += field.getFieldName() + ",";
				table.keyFieldObjectListString += field.getObjectName() + ",";
				table.keyFieldClassListString += field.getClassName() + ",";

				table.keyTypeFieldListString += field.getDataType()
						.getTypeString() + " " + field.getObjectName() + ",";
				table.questionAssignKeyFieldListString += table.getTableName()
						+ "." + field.getFieldName() + "=?" + " AND ";
			}
		}
		if (table.keyFieldListString.length() > 0) {
			table.keyFieldListString = table.keyFieldListString.substring(0,
					table.keyFieldListString.length() - 1);
			table.keyFieldObjectListString = table.keyFieldObjectListString
					.substring(0, table.keyFieldObjectListString.length() - 1);
			table.keyFieldClassListString = table.keyFieldClassListString
					.substring(0, table.keyFieldClassListString.length() - 1);
			table.keyTypeFieldListString = table.keyTypeFieldListString
					.substring(0, table.keyTypeFieldListString.length() - 1);
			table.questionAssignKeyFieldListString = table.questionAssignKeyFieldListString
					.substring(0,
							table.questionAssignKeyFieldListString.length()
									- " AND ".length());
		}
	}

	public static List<Table> getAllTables(Connection conn) {
		List<Table> tables = new ArrayList<Table>();
		Statement stmt = null;
		ResultSet rs = null;
		
		//select table_name, table_comments from user_tables where 1=1 and  tablespace_name = 'RECONFIG' 
		//select table_name,comments from user_tab_comments; 
		
		String sql ="select m.table_name,(select s.comments from user_tab_comments s where table_name = m.table_name) table_comment  from user_tables m where 1=1 and  tablespace_name = '" + CommonPara.dbSchema + "'"; 
		
		//String sql = "select table_name, table_comment from information_schema.tables where table_schema = '" + CommonPara.dbSchema + "'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Table table = new Table();
				table.setTableName(rs.getString("table_name"));
				String table_comment = rs.getString("table_comment");
				if(null == table_comment){
					table_comment = "";
				}
				table.setTableDesc(table_comment);
				tables.add(table);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return tables;
	}
}
